import sqlite3


conn_path = "data\\message.db"


# 插入数据
def insertdata(mname, chatt, chatc, chatp, chatgs, chatpath):
    conn = sqlite3.connect(conn_path)
    c = conn.cursor()
    insrtsql = 'INSERT OR IGNORE INTO message ("membername", "chattime", "chatcontent", "chattype", "groups", "path") '\
               'VALUES (?,?,?,?,?,?)'
    c.execute(insrtsql, [mname, chatt, chatc, chatp, chatgs, chatpath])
    conn.commit()
    conn.close()


# 获取所有群名数据
def groupslist():
    rs = []
    conn = sqlite3.connect(conn_path)
    c = conn.cursor()
    selectsql = 'SELECT groups FROM message GROUP BY groups ORDER BY groups'
    records = c.execute(selectsql)
    # print(records)
    for r in records:
        rs.append(r)
    # conn.commit()
    conn.close()
    return rs


# 根据群名获取成员列表
def memberlist(group_name):
    rs = []
    conn = sqlite3.connect(conn_path)
    c = conn.cursor()
    selectsql = 'SELECT membername FROM message WHERE groups LIKE "'+group_name+'" GROUP BY membername '
    records = c.execute(selectsql)
    # print(records)
    for r in records:
        rs.append(r)
    conn.close()
    return rs


# 根据成员获取内容
def contentlist(group_name, member_name):
    rs = []
    conn = sqlite3.connect(conn_path)
    c = conn.cursor()
    selectsql = 'SELECT chattime,chatcontent,chattype,path FROM message WHERE membername="' + member_name +\
                '" AND groups="'+group_name + '" ORDER BY chattime DESC '
    records = c.execute(selectsql)
    # print(records)
    for r in records:
        rs.append(r)
    conn.close()
    return rs
